KayeILSQL-04-Oracle Integrity Constraints

Contents

Based On

References

Database Design Process

Definition of Integrity Constraints

Types of Integrity Constraints

• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• CHECK Constraint
• REF Column Constraints

A Section of the ER Model of the European Parliament

Table Instance Chart

• Table Instance Chart—PRIMARY KEY
• Table Instance Chart—COMPOSITE PRIMARY KEY
• Table Instance Chart—ALTERNATE PRIMARY KEY
• Table Instance Chart—FOREIGN KEY

SQL — CREATE TABLE Statement

INLINE and OUT-OF-LINE Constraint Specifications

SQL — NOT NULL CONSTRAINT

• SQL — NOT NULL Constraint Clause
• SQL — NOT NULL Constraint in Data Dictionary

SQL — UNIQUE CONSTRAINT

• SQL — UNIQUE Constraint on a Single Column
• SQL — UNIQUE Constraint in Data Dictionary (Single Column)
• SQL — UNIQUE Constraint on Multiple Columns
• SQL — UNIQUE Constraint in Data Dictionary (Multiple Columns)

SQL — PRIMARY KEY CONSTRAINT

• SQL — PRIMARY KEY Constraint on a Single Column
• SQL — PRIMARY KEY Constraint in DD (Single Column)
• SQL — PRIMARY KEY Constraint on Multiple Columns
• SQL — PRIMARY KEY Constraint in DD (Multiple Columns)

SQL — FOREIGN KEY CONSTRAINT

• FOREIGN KEY Constraint — About
• FOREIGN KEY Constraint — REFERENCES Clause
• REFERENCES Clause — INLINE Specification
• REFERENCES Clause — OUT-OF-LINE Specification
• FOREIGN KEY Constraint — ON DELETE Clause
• ON DELETE Clause — Default Behaviour
• ON DELETE Clause — CASCADE
• ON DELETE Clause — SET NULL
• FOREIGN KEY Constraint — Data Dictionary Query 1
• FOREIGN KEY Constraint — Data Dictionary Query 2

SQL — CHECK CONSTRAINT

• CHECK Constraint — About
• SQL — CHECK Constraint Example 1
• SQL — CHECK Constraint Example 2
• SQL — CHECK Constraint in Data Dictionary
• CHECK Constraint — Design Considerations

For More on Integrity Constraints

References

The European Parliament (EP) is the parliamentary institution of the European Union (EU).

The European Parliament is elected by the citizens of the European Union to represent their interests.

Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.

Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the European Union's nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.

The European Parliament model and data used in this SQL text, are based on the elections which were held in June 2004. The European Parliament of 2004 elections had 785 members from all 27 EU countries.

The sources for the data, which are all freely obtainable from the European Union website, and from several leaflets of the EU institutions, are listed in the References section.

These sources are used to create the fictitious Business Narrative of the "Data Modelling and Normalization" Chapter.

We called this Business Narrative; "the Story of the European Union" which explains about the EU and EP, for purposes of Data Modeling.

The Story of the European Union is the last Chapter of Kaye is Learning SQL.

A Database Design is derived from an Entity Relationship(ER) Model.

During the Database Design Process, using the ER Model, we map:

• Entities into Tables
• Attributes into Columns
• UIDs into Primary Keys
• Relationships into Foreign Keys
• UIDs, Relationships, Business Rules into Constraints

In Chapter 2—Data Modeling and Normalization, we learned about "ER Modeling".

We listened to the Story of the European Union from a Business Analyst working in the "IT Organization of the European Union".

The Story of the European Union told us by the Business Analyst is also called, the Business Narrative of the Organization.

We concentrated on the European Parliament institute of the European Union.

Using this Business Narrative of the Organization, we derived;

• the Entities
• their Attributes
• their UIDs(Unique Identifiers)
• their Relationships
• the Business Rules

All this information, some of which was fictitious, enabled us to create a Data Model of the European Parliament.

At the end of the Modeling and Normalization processes, we came up with an ER(Entity Relationship) Model in Third Normal Form(3NF), which you can find down below.

According to the "Application Design Cycle" which we have seen in Chapter 2—Data Modeling and Normalization, the Database Design process comes next:

In this chapter, we will focus on the structures/objects called Integrity Constraints, and How to Create Constraints which is part of the Oracle Database Design Process.

An Integrity Constraint is a named rule that restricts the values for one or more columns in a table.

The CONSTRAINT clause is used to define an Integrity Constraint.

Oracle server uses constraints to ensure Data Integrity, i.e. to prevent invalid data entry into the database tables.

Constraints enforce Business Rules at the table level.

Constraints prevent deletion of a table if there are dependencies from other tables.

NOT NULL Constraint prohibits a database value from being NULL.

NOT NULL Constraint allows or disallows INSERTs or UPDATEs of rows containing a NULL in a specified column.

UNIQUE Constraint prohibits multiple rows from having the same value in the same column or combination of columns, but allows some values to be NULL.

PRIMARY KEY Constraint combines a "NOT NULL Constraint" and a "UNIQUE Constraint".

That is, a PRIMARY KEY Constraint prohibits multiple rows from having the same value in the same column or combination of columns, and prohibits values from being NULL.

FOREIGN KEY Constraint requires values in one table to match values in the same table or in another table.

FOREIGN KEY Constraint designates a column of the table as the FOREIGN KEY, and establishes a Relationship between "the Foreign Key of the table" and "a Primary Key or a Unique Key of another table".

The Primary Key or the Unique Key of "the other table" in this Relationship is called the Referenced Key.

CHECK Constraint requires a column value in the database to comply with a "Specified Condition".

You can find detailed information on REF Columns in the "Oracle® Database, Object-Relational Developer's Guide".

Rules for REF columns and attributes can be enforced by the use of Constraints.

In Oracle Database, a REF column or attribute can be Unconstrained or Constrained using a SCOPE clause or a Referential Constraint clause.

When a REF column is Unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.

Oracle Database DOES NOT ENSURE that the object references stored in such columns point to valid and existing row objects. Therefore, REF columns may contain object references that DO NOT point to any existing row object. Such REF values are referred to as Dangling References.

A REF column CAN BE CONSTRAINED with a REFERENTIAL Constraint similar to the specification for Foreign Keys. The rules for Referential Constraints apply to such columns. That is, the object reference stored in these columns MUST point to a valid and existing row object in the specified object table.

PRIMARY KEY Constraints CANNOT be specified for REF columns.

However, you CAN specify NOT NULL Constraints for REF columns.

For more information on REF Column Constraints, please refer to the "Oracle® Database, Object-Relational Developer's Guide".

We will use a Section of Our ER Model of the European Parliament seen below, for Constraint Creation Examples.

This model contains only a few of the Entities from Our ER Model of the European Parliament, which we have already developed in Chapter 2—Data Modeling and Normalization.

During the Database Design Process, using the ER Model, we mapped Entities into Tables:

• Entity POLITICAL_GROUP is mapped into the table POLITICAL_GROUPS.
• Entity MEP is mapped into the table MEPS.
• Entity COUNTRY is mapped into the table COUNTRIES.

A Table Instance Chart is used during the Database Design Process for preparing the SQL Scripts that create the Database Objects.

A Table Instance Chart is part of the Database Design Process and it is one of the "Preparation Steps" BEFORE "the ER Model elements are turned into Database Objects".

When working on a Table Instance Chart;

• We determine Tables using Entities
• We determine Columns using Attributes
• We determine Primary Keys using UIDs
• We determine Foreign Keys using Relationships
• We determine Constraints using UIDs, Relationships and Business Rules
• We decide on the Data Type and Length of the Columns
• We may change the Names of the Columns
• We may add New Columns

Below is the Table Instance Chart for the MEPS table.

A Primary Key (PK) is a "Column or a Set of Columns " that UNIQUELY IDENTIFY Each Row in a Database Table.

Each Table MUST have a Primary Key (PK).

The Primary Key (PK) MUST be UNIQUE.

The Primary Key (PK) MAY NOT be NULL.

The UID of an Entity in an ER Model, will map into a Primary Key (PK) in its corresponding Table in the Database.

In the Table Instance Chart for the MEPS table, we can see that the column mep_id is designated as the Primary Key (PK).

Each row in table MEPS can be UNIQUELY IDENTIFIED by the column mep_id.

A Primary Key consisting of Multiple Columns is called a Composite Primary Key or a Compound Primary Key.

• The COLUMNS of a Composite Primary Key MUST be UNIQUE in Combination, although the Individual Columns CAN have Duplicates.
• NO PART of a Composite Primary Key may be NULL, i.e. NO PART of a Composite Primary Key MAY LACK a Value.

In the Table Instance Chart for the MEPS table, we can see NO COLUMNS designated as a Composite Primary Key.

The single column mep_id is designated as the Primary Key (PK) for the MEPS table.

A table can have several Candidate Primary Keys.

A CANDIDATE Primary Key is a column or combination of columns that CAN Serve as the Primary Key for the table.

We select One of the Candidate Primary Keys to be the Primary Key for the table.

The Other Candidates MAY Become Alternate Primary Keys or Unique Keys.

We have already selected the "mep_id" column as the Primary Key for the MEPS table.

The column "email" is another Candidate Primary Key for the MEPS table.

NO TWO ROWS in the MEPS table can have identical emails.

The values of the "email" column MUST be NOT NULL and MUST be UNIQUE.

Therefore, the column "email" CAN BE an Alternate Primary Key for the MEPS table.

Below is the "email" column of the Table Instance Chart for the MEPS table.

The SQL Script which creates the MEPS table, will ensure that;

• the values of the "email" column CAN NEVER BE NULL (NN)
• the values of the "email" column WILL ALWAYS BE UNIQUE (U)

These are achieved by creating a NOT NULL Constraint (NN) and a UNIQUE Constraint (U) on the "email" column, within the SQL Script.

Relationships in an ER Model are mapped into Foreign Keys in the Database.

As we can see in the ER Model of the European Parliament, there are a few Relationships regarding the Entities MEP, POLITICAL GROUP and the COUNTRY.

From the point of view of the MEPS table under consideration, these Relationships are;

• Each Member of the Parliament (MEP) MUST be a citizen of One and Only One EU Country (COUNTRY).
• Each Member of the Parliament (MEP) MUST belong to One and Only One Political Group of the European Parliament (POLITICAL GROUP).
• Each Member of the Parliament (MEP) MUST be chaired by a chair or a co-chair who is also a Member of the Parliament (MEP) — This last Relationship tells us that MEP is a Self Referencing Entity.

These Relationships are mapped into Foreign Keys in the Database Design stage.

A Foreign Key (FK) is a "Column or a Combination of Columns" in One Table that REFERS to a Primary Key in the " Same Table or in Another Table".

Below is the Table Instance Chart for the MEPS table, showing the FOREIGN KEY columns only.

Below is the SQL CREATE TABLE Statement which creates the MEPS table in our Database.

To reach this SQL CREATE TABLE Statement, we have gone through many many steps:

• We listened to the Story of our Business which we also call the Business Narrative.
• Using this Business Narrative, we created the Data Model of Our Business. We rigorously went through the steps of Normalization, and we created an ER Model in Third Normal Form (3NF), which has No Data Redundancy.
• Using Our ER Model, we mapped Entities into Tables, Attributes into Columns, UIDs into Primary Keys, Relationships into Foreign Keys, and UIDs, Relationships and Business Rules into Constraints, we created Table Instance Charts and Designed our Database.
• Now we can create the SQL Statements on top of our Database Design which will be used to create our Database.
• Our Company/Institution can Build Up Business Information Systems on top of that Database.

Our SQL CREATE TABLE Statement contains CONSTRAINT Clauses to create several Types of Constraints that we will now examine.

INLINE SPECIFICATION is, when you define an Integrity Constraint as part of an "Individual Column Definition" of an SQL CREATE TABLE Statement.

OUT-OF-LINE SPECIFICATION is, when an Integrity Constraint is declared as part of the "Table Definition" of an SQL CREATE TABLE Statement.

NOT NULL Constraints MUST be declared INLINE.

All Other Integrity Constraints CAN BE declared either INLINE or OUT-OF-LINE.

OUT-OF-LINE Constraint Definition can appear BEFORE or AFTER the Column Definitions.

The lines seen below are part of our SQL CREATE TABLE Statement for the MEPS table.

These lines define the column LAST_NAME (last_name VARCHAR2(120)).

At the same time, they define a NOT NULL Constraint on the column LAST_NAME (CONSTRAINT last_name_nn NOT NULL).

The name of this NOT NULL Constraint is LAST_NAME_NN.

This NOT NULL Constraint on the column LAST_NAME ensures that there will ALWAYS be a NONE NULL value in this column.

You MUST define a NOT NULL Constraint using INLINE SPECIFICATION, i.e. as part of the column definition.

If you specify neither NOT NULL, nor NULL, then the Default is NULL.

• The NULL Keyword by itself DOES NOT actually Define an Integrity Constraint.
• But, you can specify it to EXPLICITLY PERMIT a column to contain NULLs.

You can query the newly created NOT NULL Constraints in the Data Dictionary.

The two SQL Statements below, query the NOT NULL Constraints in two different ways.

• Constraint Type 'C' is used for "NOT NULL Constraints" and "CHECK Constraints".
• The query result above displays two "NOT NULL Constraints" on the MEPS table.
• The SEARCH_CONDITION Column of the USER_CONSTRAINTS Data Dictionary table helps us understand the type of the constraint — whether "NOT NULL" or "CHECK".

The lines seen below are part of our SQL CREATE TABLE Statement for the MEPS table.

These lines define the column EMAIL (email VARCHAR2(240)).

At the same time, they define;

• a NOT NULL Constraint named EMAIL_NN (CONSTRAINT email_nn NOT NULL)
• a UNIQUE Constraint named EMAIL_UNIQUE (CONSTRAINT email_unique UNIQUE)

In this section, we will investigate the UNIQUE Constraint on the column EMAIL — a single column.

The UNIQUE Constraint ensures that every value in a column is UNIQUE.

This means that no two rows of a table can have the same value in a specified column — NO DUPLICATE VALUES are allowed in the same column.

The UNIQUE Constraint ALLOWS the input of NULL Values in a column.

This is because;

• Each NULL is Unique
and
• No TWO NULL Values are EVER EQUAL to each other

However, in this case, two different types of constraints are defined on the EMAIL column:

email   VARCHAR2(240)
    CONSTRAINT email_nn NOT NULL
    CONSTRAINT email_unique UNIQUE,

These "two constraints together" ensure that;

• One: The email column ALWAYS has a none-null value (CONSTRAINT email_nn NOT NULL) in it.
and
• Two: This none-null value is Unique (CONSTRAINT email_unique UNIQUE) in the MEPS table.

Also, because of these two constraints, the column email becomes a Candidate Primary Key.

As we already have a Primary Key (the mep_id column), the column "email" is chosen as an Alternate Primary Key.

This UNIQUE Constraint on the single column "email" is specified INLINE, as part of the Column Definition.

You can query this UNIQUE Constraint in the Data Dictionary.

Constraint Type 'U' is used for "UNIQUE Constraints".

The query result above, displays all the "UNIQUE Constraints" on "MEPS" table — "UC.CONSTRAINT_TYPE='U'".

We can see that there is only one UNIQUE Constraint defined on the "MEPS" table.

We will use the SQL Create Table Statement of the MEP_PG_ROLES table, as an example on "UNIQUE Constraint on Multiple Columns".

Below is a section of Our ER Model of the European Parliament for this purpose.

Below is the SQL CREATE TABLE Statement which creates the MEP_PG_ROLES table in our Database.

The lines below which are part of the SQL CREATE TABLE Statement, define a UNIQUE Constraint named MEP_PG_ROLES_UN on two columns of the table:

• mep_id
• pg_role_id

This UNIQUE Constraint named MEP_PG_ROLES_UN on the multiple columns "mep_id" and "pg_role_id" is specified OUT-OF-LINE, as part of the Table Definition.

A UNIQUE Constraint on "multiple columns" such as the one just created on "mep_id" and "pg_role_id" can also be called a Composite Unique Key Constraint.

A Composite Unique Key Constraint MUST BE Defined OUT-OF-LINE, as part of the Table Definition.

To Satisfy a Composite Unique Key Constraint:

NO TWO ROWS in the table can have "the Same Combination of Values" in the "Composite Unique Key Columns".
Any row that contains "NULLS in All UNIQUE Constraint Columns" automatically SATISFIES the "Composite Unique Key Constraint".
You CANNOT have Identical Values in the "NON-NULL Columns" of the "Composite UNIQUE Key Constraint".
This means that;
Two rows that contain
• "NULLs for One or More Key Columns"
and
• "the Same Combination of Values for the Other Key Columns"
VIOLATE the Constraint.

Let's query this Composite Unique Key Constraint in the Data Dictionary.

The lines seen below are part of our SQL CREATE TABLE Statement for the MEPS table.

These lines define the column MEP_ID (mep_id NUMBER(10)).

At the same time, they define a PRIMARY KEY Constraint on the column MEP_ID (CONSTRAINT mep_id_pk PRIMARY KEY).

The name of this PRIMARY KEY Constraint is MEP_ID_PK.

This PRIMARY KEY Constraint is specified INLINE as part of the column definition.

This PRIMARY KEY Constraint named MEP_ID_PK, enables each row to be uniquely identified by the values in the MEP_ID column.

The values of the MEP_ID column:

• MUST BE NOT NULL
• MUST BE UNIQUE

A PRIMARY KEY Constraint combines a NOT NULL Constraint and a UNIQUE Constraint in one Declaration.

Therefore, to SATISFY a PRIMARY KEY Constraint:

We can query this PRIMARY KEY Constraint in the Data Dictionary.

For PRIMARY KEY Constraints, Constraint Type 'P' is used.

The result of the query is displayed above. There is always ONE Primary Key Constraint for a Table, made up of One or More Columns.

In this case, the PRIMARY KEY Constraint (CONSTRAINT_TYPE='P') is made up of a Single Column — MEP_ID.

Below is the SQL CREATE TABLE Statement for the table MEPS_HISTORY.

The lines below which are part of the SQL CREATE TABLE Statement, define a PRIMARY KEY Constraint named MEPS_HISTORY_PK on the two columns of the table MEPS_HISTORY:

• mep_id
• start_date

This PRIMARY KEY Constraint named MEPS_HISTORY_PK on the multiple columns "mep_id" and "start_date" is specified OUT-OF-LINE, as part of the Table Definition.

A PRIMARY KEY Constraint on "multiple columns" such as the one just created on the "mep_id" and "start_date" columns, is also called a Composite Primary Key Constraint.

A Composite Primary Key Constraint MUST BE defined OUT-OF-LINE as part of the Table Definition.

We can query this Composite Primary Key Constraint in the Data Dictionary as follows.

A Foreign Key Constraint, which is also called a Referential Integrity Constraint, designates a column as the Foreign Key, and establishes a Relationship between that Foreign Key and a specified Primary Key or Unique Key, called the Referenced Key.

A Composite Foreign Key designates a combination of columns as the Foreign Key.

The table containing the Foreign Key is called the CHILD Object.

The table containing the Referenced Key is called the PARENT Object.

The Foreign Key and the Referenced Key can be in the SAME Table.

In this case, the PARENT Table and the CHILD Table are the SAME Table.

If you identify only the PARENT Table and omit the Foreign Key Column name, then the Foreign Key automatically References the Primary Key of the PARENT Table.

The corresponding column or columns of the Foreign Key and the Referenced Key MUST match "in Order" and "in Data Type".

You CAN define a Foreign Key Constraint "ON a Single Column", either INLINE or OUT-OF-LINE.

You MUST specify a Composite Foreign Key Constraint OUT-OF-LINE.

Foreign Key Constraints use the REFERENCES Clause.

When you specify a Foreign Key Constraint INLINE, you ONLY need the REFERENCES Clause.

When you specify a Foreign Key Constraint OUT-OF-LINE, you MUST ALSO specify the FOREIGN KEY Keyword and one or more columns.

Below is an example of an INLINE Specification of a FOREIGN KEY Constraint (PG_ID_FK).

pg_id NUMBER(10)
    CONSTRAINT pg_id_fk
        REFERENCES political_groups(pg_id),

Only the REFERENCES Clause is used.

Only the REFERENCES Clause is SUFFICIENT enough to define a FOREIGN KEY Constraint in an INLINE Specification.

Full SQL Statement of this constraint (PG_ID_FK), can be seen below.

/* MEPS */
CREATE TABLE meps (
    mep_id NUMBER(10)
        CONSTRAINT mep_id_pk PRIMARY KEY,
    first_name VARCHAR2(120),
    last_name VARCHAR2(120)
        CONSTRAINT last_name_nn NOT NULL,
    salary NUMBER(9, 2),
    bonus_pct NUMBER(5, 4),
    email VARCHAR2(240)
        CONSTRAINT email_nn NOT NULL
        CONSTRAINT email_unique UNIQUE,
    tel VARCHAR2(20),
    internet VARCHAR2(120),
    start_date DATE,
    country_id VARCHAR2(4)
        CONSTRAINT country_id_fk
            REFERENCES countries ( country_id ),
    pg_id NUMBER(10)
        CONSTRAINT pg_id_fk
            REFERENCES political_groups ( pg_id ),
    pg_chair_or_cochair1  NUMBER(10)
        CONSTRAINT pg_chair_cochair1_fk
            REFERENCES meps ( mep_id ),
    pg_cochair2 NUMBER(10)
        CONSTRAINT pg_cochair2_fk
            REFERENCES meps ( mep_id )
);

• POLITICAL_GROUPS table is the PARENT Table or the Referenced Table.
• MEPS table is the CHILD Table or the Dependent Table.
• PG_ID column of the MEPS table is the FOREIGN Key.
• PG_ID column of the POLITICAL_GROUPS table is the Referenced Key.

Below is an example of an OUT-OF-LINE Specification of a FOREIGN KEY Constraint (PG_ID_FK).

,CONSTRAINT pg_id_fk FOREIGN KEY (pg_id)
    REFERENCES political_groups ( pg_id )

"REFERENCES Clause + FOREIGN KEY Keyword" are BOTH used.

NOT Only the "REFERENCES Clause", But ALSO the "FOREIGN KEY Keyword" MUST BE USED in an OUT-OF-LINE Specification.

Full SQL Statement of this constraint (PG_ID_FK), can be seen below.

/* MEPS */
CREATE TABLE meps (
    mep_id NUMBER(10)
        CONSTRAINT mep_id_pk PRIMARY KEY,
    first_name VARCHAR2(120),
    last_name VARCHAR2(120)
        CONSTRAINT last_name_nn NOT NULL,
    salary NUMBER(9, 2),
    bonus_pct NUMBER(5, 4),
    email VARCHAR2(240)
        CONSTRAINT email_nn NOT NULL
        CONSTRAINT email_unique UNIQUE,
    tel VARCHAR2(20),
    internet VARCHAR2(120),
    start_date DATE,
    country_id VARCHAR2(4)
        CONSTRAINT country_id_fk
            REFERENCES countries ( country_id ),
    pg_id NUMBER(10),
    pg_chair_or_cochair1  NUMBER(10)
        CONSTRAINT pg_chair_cochair1_fk
            REFERENCES meps ( mep_id ),
    pg_cochair2 NUMBER(10)
        CONSTRAINT pg_cochair2_fk
            REFERENCES meps ( mep_id )
    ,CONSTRAINT pg_id_fk FOREIGN KEY (pg_id)
        REFERENCES political_groups ( pg_id )
    );

• POLITICAL_GROUPS table is the PARENT Table or the Referenced Table.
• MEPS table is the CHILD Table or the Dependent Table.
• PG_ID column of the MEPS table is the FOREIGN Key.
• PG_ID column of the POLITICAL_GROUPS table is the Referenced Key.

If you OMIT the ON DELETE Clause, then Default Behaviour takes action.

If you OMIT the ON DELETE Clause, and Default Behaviour takes place, then Oracle DOES NOT ALLOW you to DELETE Referenced Key values in the PARENT Table that have Dependent Rows in the CHILD Table.

Let's assume that the FOREIGN Key Constraint (PG_ID_FK) is created using the SQL Statement below:

/* MEPS */
CREATE TABLE meps (
    mep_id NUMBER(10)
        CONSTRAINT mep_id_pk PRIMARY KEY,
    first_name VARCHAR2(120),
    last_name VARCHAR2(120)
        CONSTRAINT last_name_nn NOT NULL,
    salary NUMBER(9, 2),
    bonus_pct NUMBER(5, 4),
    email VARCHAR2(240)
        CONSTRAINT email_nn NOT NULL
        CONSTRAINT email_unique UNIQUE,
    tel VARCHAR2(20),
    internet VARCHAR2(120),
    start_date DATE,
    country_id VARCHAR2(4)
        CONSTRAINT country_id_fk
            REFERENCES countries ( country_id ),
    pg_id NUMBER(10)
        CONSTRAINT pg_id_fk
            REFERENCES political_groups ( pg_id ),
    pg_chair_or_cochair1  NUMBER(10)
        CONSTRAINT pg_chair_cochair1_fk
            REFERENCES meps ( mep_id ),
    pg_cochair2 NUMBER(10)
        CONSTRAINT pg_cochair2_fk
            REFERENCES meps ( mep_id )
    );
    

These are the lines where the constraint (PG_ID_FK) is created:

pg_id NUMBER(10)
    CONSTRAINT pg_id_fk
        REFERENCES political_groups(pg_id),

As we can see the ON DELETE Clause is OMITTED.

There is NO ON DELETE Clause.

Therefore, Default Behaviour will take action.

If we want to remove/delete a row from the PARENT Table which is the POLITICAL_GROUPS table, with a specific PG_ID value that has CHILD Rows in the CHILD Table which is the MEPS table, then Oracle WILL NOT ALLOW this happen.

Here is the output in SQL Developer.

If now we try to delete the row with the PG_ID value of 20 in the PARENT Table, which is the POLITICAL_GROUPS, Oracle will throw an ERROR.

This is because, there are 288 CHILD Rows in the MEPS table with a PG_ID value of 20.

We can see the SQL Statement and the resulting error below.

This is the ON DELETE Clause Default Behaviour in action when the ON DELETE Clause is OMITTED.

Specify CASCADE if you want Oracle to remove DEPENDENT FOREIGN Key Rows automatically/implicitly.

ON DELETE CASCADE causes the DEPENDENT Rows in the CHILD Table to be deleted automatically/implicitly by Oracle, when a row in the PARENT Table is deleted.

In our example, the FOREIGN KEY Constraint is "PG_ID_FK":

• The PARENT Table is POLITICAL_GROUPS.
• The CHILD Table or the DEPENDENT Table is MEPS.

SELECT * 
FROM POLITICAL_GROUPS
ORDER BY PG_ID    

The query above retrieves all the rows in the PARENT Table which is POLITICAL_GROUPS.

Let's focus on the row with PG_ID=20.

This is the political group named 'Group of the European People's Party(Christian Democrats) and European Democrats' with the PG_CODE='EPP-ED'.

We already know from a previous query that there are 288 Members of the Parliament belonging to this Political Group.

This information is held in the CHILD Table or the DEPENDENT Table — MEPS.

Below is a query to retrive all the Members of the Parliament belonging to the Political Group 'EPP-ED' (PG_ID=20). Only some of the columns are selected.

SELECT M.MEP_ID,
M.PG_ID,
M.FIRST_NAME,
M.LAST_NAME,
M.COUNTRY_ID,
M.INTERNET
FROM MEPS M
WHERE 1=1
AND M.PG_ID=20
ORDER BY M.MEP_ID

and here is the result in SQL Developer:

If we specify the ON DELETE CASCADE Clause when defining the FOREIGN KEY "PG_ID_FK", and at some stage, we DELETE the row with PG_ID=20 from the PARENT Table POLITICAL_GROUPS, then all of the 288 DEPENDENT rows in the CHILD Table MEPS, with PG_ID=20 will be automatically/implicitly DELETED by Oracle.

Below is the Constraint Definition with the ON DELETE CASCADE Clause within the CHILD Table MEPS:

pg_id NUMBER(10)
    CONSTRAINT pg_id_fk
        REFERENCES political_groups(pg_id)
        ON DELETE CASCADE,

This is how ON DELETE CASCADE works.

ON DELETE SET NULL works similar to ON DELETE CASCADE.

Only, instead of removing the DEPENDENT FOREIGN Key Rows automatically/implicitly, Oracle converts the DEPENDENT FOREIGN Key Values to NULL automatically/implicitly, when a row in the PARENT Table is deleted.

Let's take the same example in the ON DELETE CASCADE section.

If we specify the ON DELETE SET NULL Clause when defining the FOREIGN KEY "PG_ID_FK", and at some stage, we DELETE the row with PG_ID=20 from the PARENT Table POLITICAL_GROUPS, then Oracle will automatically/implicitly convert the PG_ID values of all the 288 DEPENDENT rows in the CHILD Table MEPS, from 20 to NULL.

Below is the Constraint Definition with the ON DELETE SET NULL Clause within the CHILD Table MEPS:

pg_id NUMBER(10)
    CONSTRAINT pg_id_fk
    REFERENCES political_groups(pg_id)
    ON DELETE SET NULL,

This is how ON DELETE SET NULL works.

We can query a FOREIGN KEY Constraint in the Data Dictionary.

For FOREIGN KEY Constraints, Constraint Type 'R' is used.

'R' stands for Referential Integrity.

We will now query the DD—Data Dictionary for all the FOREIGN Key Constraints of the MEPS table.

The SQL query above retrieves all the FOREIGN KEY / REFERENTIAL INTEGRITY Constraints of the MEPS table.

However, it only displays; the "Names of the Constraints "and the "Foreign Key Columns".

It DOES NOT give any information on the PARENT Table, and the REFERENCED KEY of the PARENT Table.

We need another SQL query to find this information, which is in the next section.

The query below retrieves almost everything we would like to know about a Foreign Key;

• Name of the Child Table ("Child Table")
• Type of the Constraint ("Child Constraint Type")
• Name of the Foreign Key Constraint ("Child Constraint Name")
• Name of the Foreign Key Column ("Foreign Key")
• Name of the Parent Table ("Parent Table")
• Type of the Referenced Key Constraint ("Parent Constraint Type")
• Name of the Referenced Key Constraint ("Parent Constraint Name")
• Name of the Referenced Key Column ("Referenced Key")

This query uses the two Data Dictionary tables;

• USER_CONSTRAINTS
• USER_CONS_COLUMNS

This sums up querying Foreign Keys in the Data Dictionary.

A CHECK Constraint specifies a CONDITION that each row in the table MUST Satisfy.

To Satisfy the CHECK Constraint, each row in the table MUST make the CONDITION;

• either TRUE
• or UNKNOWN(due to a NULL)

When Oracle evaluates a CHECK Constraint CONDITION for a particular row, any "Column Names" in the CONDITION "REFER to the Column Values in that row."

The syntax for INLINE and OUT-OF-LINE specification of CHECK Constraints are the same.

However, INLINE specification can REFER only to the "Column currently being defined", whereas OUT-OF-LINE specification can REFER to "Multiple Columns".

Let's assume that we want to restrict the values input to the column PG_CODE, with the following values:

• 'EPP-ED'
• 'PES'
• 'Greens/EFA'
• 'GUE/NGL'
• 'IND/DEM'
• 'ALDE'
• 'UEN'
• 'NI' for "non-attached Members — non-inscrits (NI)"

In this case, we can define a CHECK Constraint on the column PG_CODE as follows:

• A CHECK Constraint is defined on the column PG_CODE.

• The name of this CHECK Constraint is CHECK_PG_CODE.

• The CHECK Condition

  (pg_code IN ('EPP-ED', 
  'PES',
  'Greens/EFA',
  'GUE/NGL',
  'IND/DEM',
  'ALDE',
  'UEN',
  'NI'))    
  

  ensures that the value of the PG_CODE column can only be 'EPP-ED', 'PES', 'Greens/EFA', 'GUE/NGL', 'IND/DEM', 'ALDE', 'UEN' or 'NI'.

• No rows with other values in this column will be allowed into the database.

However, this CHECK Constraint allows NULL values in the column PG_CODE.

This is because, if the input value is NULL, then the expression

(pg_code IN ('EPP-ED', 
'PES',
'Greens/EFA',
'GUE/NGL',
'IND/DEM',
'ALDE',
'UEN',
'NI'))    

Here is another CHECK Constraint example.

This CHECK Constraint example is on the table MEPS_HISTORY.

Below is the SQL CREATE TABLE Statement for the table MEPS_HISTORY.

The lines below

,constraint meps_history_dates_check
check(start_date < end_date)

which are part of the SQL CREATE TABLE Statement, define a CHECK Constraint named MEPS_HISTORY_DATES_CHECK on the two columns of the table MEPS_HISTORY:

• start_date
• end_date

The CHECK Constraint named MEPS_HISTORY_DATES_CHECK ensures that the value in the "start_date" column is always LESS THAN (<) the value in the "end_date" column, within a row.

This CHECK Constraint named MEPS_HISTORY_DATES_CHECK on the multiple columns "start_date" and "end_date" is specified OUT-OF-LINE, as part of the Table Definition.

The SQL Statement below, queries the constraints of the MEPS_HISTORY table with CONSTRAINT_TYPE='C'.

The query is on the Data Dictionary tables, USER_CONSTRAINTS and USER_CONS_COLUMNS.

Here is the result of the query in SQL Developer.

CONSTRAINT_TYPE column of the USER_CONSTRAINTS table, tells us the "type" of the constraint.

CONSTRAINT_TYPE='C' tells us that the Constraint is:

either
• a CHECK Constraint
or
• a NOT NULL Constraint

The SEARCH_CONDITION column of the USER_CONSTRAINTS table makes it clear whether the Constraint is a "NOT NULL Constraint" or not.

If it says "COLUMN_NAME" IS NOT NULL in the SEARCH_CONDITION column, then this is a "NOT NULL Constraint", else a "CHECK Constraint".

Constraints marked with a rectangle are "NOT NULL Constraints".

Here are the result rows of the query displaying the CHECK Constraint "MEPS_HISTORY_DATES_CHECK".

The SEARCH_CONDITION column is the same as the CHECK Condition we defined in the SQL CREATE TABLE Statement of the MEPS_HISTORY:

,constraint meps_history_dates_check
check(start_date < end_date)

... and now, a breath of fresh air.

A table can have multiple CHECK Constraints.

When a constraint is violated, Oracle returns an error identifying the constraint.

Such an error more precisely identifies the violated business rule, if the identified constraint enables a single business rule.

CHECK Constraints are used when Integrity Rules need to be enforced based on Logical Expressions — such as Comparisons.

It is NOT ADVISABLE to use CHECK Constraints, when any of the Other Types of Integrity Constraints CAN PROVIDE the necessary checking.

You can refer to the Oracle Documentation if you need more detailed information on Integrity Constraints.

You can find a list of these documents in the References.

Please keep in mind that the names of the documents may change in time.

• ISBN 92-79-03653-X , How the European Union works: Booklet by EU Publications Office
• ISBN 978-92-824-2203-8, Information handbook of the Council of the European Union, DG Press, General Secretariat of the Council
• European Union

  https://europa.eu/european-union/index_en

• European Council

  https://www.consilium.europa.eu/en/

• European Parliament

  https://www.europarl.europa.eu/portal/en

• The Official Directory of the European Union (Who is Who)

  https://op.europa.eu/en/web/who-is-who